Athenaでカンマ区切りの複数カラムのデータを縦持ちにする

データアナリティクス事業本部のueharaです。

今回はAmazon Athenaで、カンマ区切りの複数カラムのデータを縦持ちにするという処理をしてみたいと思います。

何がしたいのか？

以下のようなテーブルを

items	prices	nums
aaa,bbb,ccc	100,200,300	1,2,3
ddd,eee,fff	400,500,600	4,5,6

次のような形にしたいです。

item	price	num
aaa	100	1
bbb	200	2
	(中略)
eee	500	5
fff	600	6

実現方法

結論から言うと、 CROSS JOIN UNNEST と SPLIT の組み合わせで実現できます。

まずは事前準備として、冒頭の変換前のテーブルを一時テーブルとして作成します。

WITH tmp AS (
  SELECT 'aaa,bbb,ccc' AS items, '100,200,300' AS prices, '1,2,3' AS nums
  UNION ALL
  SELECT 'ddd,eee,fff' AS items, '400,500,600' AS prices, '4,5,6' AS nums
)
-- データ確認
SELECT *
FROM tmp

上記クエリを実行すると、以下のように表示されるかと思います。

データが準備できたので、実際にCROSS JOIN UNNEST と SPLIT を利用してこのデータを縦持ちにしてみます。

WITH tmp AS (
  SELECT 'aaa,bbb,ccc' AS items, '100,200,300' AS prices, '1,2,3' AS nums
  UNION ALL
  SELECT 'ddd,eee,fff' AS items, '400,500,600' AS prices, '4,5,6' AS nums
)
-- 縦持ちに変換
SELECT
  item,
  price,
  num
FROM tmp
CROSS JOIN UNNEST (
  SPLIT(items, ','),
  SPLIT(prices, ','),
  SPLIT(nums, ',')
) t(item, price, num)

結果は次のようになり、縦持ちに変換できていることが分かります。

ちなみに、 UNNESTはFROM句においてCROSS JOINを省略して使用することができます。

補足

カラム間でSPLITされた配列の要素数が異なる場合は、エラーにはなりませんが足りない要素数分はNULLとなります。

WITH tmp AS (
  SELECT 'aaa,bbb,ccc' AS items, '100,200,300' AS prices, '1,2' AS nums
  UNION ALL
  SELECT 'ddd,eee,fff' AS items, '400,500' AS prices, '4,5,6' AS nums
)
-- 縦持ちに変換
SELECT
  item,
  price,
  num
FROM tmp
CROSS JOIN UNNEST (
  SPLIT(items, ','),
  SPLIT(prices, ','),
  SPLIT(nums, ',')
) t(item, price, num)

最後に

今回はAmazon Athenaで、カンマ区切りの複数カラムのデータを縦持ちにするという処理をしてみました。

参考になりましたら幸いです。

参考文献

• ネストされた配列のフラット化
• String Functions and Operators | Presto 0.279 Documentation